package com.tsd.common.controller;

import cn.afterturn.easypoi.entity.ImageEntity;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.entity.vo.BaseEntityTypeConstants;
import cn.hutool.json.JSONUtil;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.TypeReference;
import com.tsd.common.entity.CommonFile;
import com.tsd.common.entity.CommonFileExt;
import com.tsd.common.service.CommonFileService;
import com.tsd.common.vo.ExportExcelConvertVo;
import com.tsd.common.vo.MultiSheetRequestVo;
import com.tsd.core.config.Config;
import com.tsd.core.constants.ConfigKeyConstants;
import com.tsd.core.constants.Constants;
import com.tsd.core.excel.export.ExcelExportHelper;
import com.tsd.core.excel.export.Export;
import com.tsd.core.excel.impexcel.ExcelConfigFile;
import com.tsd.core.excel.impexcel.ExcelFileUtil;
import com.tsd.core.excel.impexcel.ExcelObject;
import com.tsd.core.utils.*;
import com.tsd.core.vo.ErrorCode;
import com.tsd.core.vo.HlpException;
import com.tsd.core.vo.HttpResult;
import com.tsd.core.vo.PageBean;
import com.tsd.system.controller.SystemBaseController;
import com.tsd.system.entity.SysUserExt;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xddf.usermodel.chart.*;
import org.apache.poi.xssf.usermodel.XSSFChart;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTDLbls;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.*;


/**
 * 模版导出
 *
 * @author zsy
 */
@Controller
@Scope("prototype")
@RequestMapping("/api/v2")
public class CommonExcelController extends SystemBaseController {

    private static final Logger LOGGER = LoggerFactory.getLogger(CommonExcelController.class);

    private static final WebUtils WEB_UTILS = WebUtils.getInstance();

    private static final String PARAMS_KEY_CUSTOM = "custom";

    private static final Integer STATE_FILE_NOT_SAVE = 0;

    private static final Integer STATE_FILE_SAVE = 1;

    public static final TypeReference<HttpResult<Object>> TYPE_REFERENCE = new TypeReference<HttpResult<Object>>() {
    };

    @Resource
    private CommonFileService commonFileService;

    /**
     * 通用Excel导出
     * 参数：title_field 标题定义
     * {name: '列名称', key: '列对应数据字段名称', width: 列宽, height: 行高, format: '导出日期格式', isStatistics: '是否合计，true|false' list: '子标题', type: '图片列为2，数字列10，其他为空'}
     * 参数：url 请求路径
     * 参数：params 请求参数
     * 参数：output_name 文件名称
     * 参数：convert 数据翻译定义（{'需要翻译的字段名称': [{name: '翻译名称', value: '字段值'}]}）
     * 参数：pageNo 页码 （查询单页使用）
     * 参数：pageSize 每页记录数（查询单页使用）
     *
     * @return
     */
    @RequestMapping(value = "/exportExcel.action", method = {RequestMethod.GET, RequestMethod.POST})
    public void exportExcel(HttpServletResponse response) throws IOException {
        try {
            SysUserExt opUser = super.getSysUser();
            Map<String, Object> map = super.getParams(request);
            // 模版名称
            String titleField = HlpUtils.getString(map, "title_field");
            String url = HlpUtils.getString(map, "url");
            String params = HlpUtils.getString(map, "params");
            String outputName = HlpUtils.getString(map, "output_name");
            String convert = HlpUtils.getString(map, "convert");
            Integer pageNo = HlpUtils.getInteger(map, "pageNo");
            Integer pageSize = HlpUtils.getInteger(map, "pageSize");
            Integer saveFile = HlpUtils.getInteger(map, "save_file", STATE_FILE_NOT_SAVE);
            super.checkEmpty(titleField, "标题定义：title_field不能为空");
            super.checkEmpty(url, "请求路径：url不能为空");
            super.checkEmpty(params, "请求参数：params不能为空");
            super.checkEmpty(outputName, "文件名称：output_name不能为空");
            if (!HlpUtils.isEmpty(outputName)) {
                outputName = outputName.replaceAll("\\.xlsx", "");
                outputName = outputName.replaceAll("\\.xls", "");
            }
            if (!HlpUtils.isEmpty(pageNo) || !HlpUtils.isEmpty(pageSize)) {
                super.checkEmpty(pageNo, "页码：pageNo不能为空");
                super.checkEmpty(pageSize, "每页记录数：pageSize不能为空");
            }
            List<ExcelExportEntity> entityList = JSON.parseArray(titleField, ExcelExportEntity.class);
            //设置databaseFormat默认值，返回图片类型字段
            List<String> picFieldList = this.procExcelExportEntity(entityList);
            // 获取导出数据
            Map<String, Object> headers = getExportHeader(map, opUser);
            List<Object> dataList = this.getExportDataList(url, params, headers, pageNo, pageSize);
            // 数据翻译
            List<JSONObject> convertList = this.convertDataList(dataList, convert);

            //图片替换
            if (!HlpUtils.isEmptyList(picFieldList)) {
                convertList = this.convertPicData(convertList, picFieldList, false);
            }
            convertList = this.procChildrenHeader(convertList, entityList);

            ExportParams exportParams = new ExportParams();
            exportParams.setSheetName(outputName);
            exportParams.setType(ExcelType.XSSF);
            // 获取excel工作流
            Workbook workbook = ExcelExportUtil.exportExcel(exportParams, entityList, convertList);
            procFormulaCell(workbook);
            this.procExportExcel(response, workbook, saveFile, outputName);
        } catch (Exception e) {
            LOGGER.error(ExceptionUtil.getStackTraceFromException(e));
            HttpResult<Object> result = new HttpResult<>();
            result.setCode(ErrorCode.CODE_PROC_ERROR);
            result.setMessage(e.getMessage());
            response.sendError(200, JSON.toJSONString(result));
        }
    }


    private List<JSONObject> procChildrenHeader(List<JSONObject> convertList, List<ExcelExportEntity> entityList) {
        if (HlpUtils.isEmptyList(convertList)) {
            return convertList;
        }
        boolean hasChildren = ListUtil.anyMatch(entityList, t -> !HlpUtils.isEmptyList(t.getList()));
        if (!hasChildren) {
            return convertList;
        }
        List<JSONObject> dataList = new ArrayList<>();
        for (JSONObject object : convertList) {
            for (ExcelExportEntity entity : entityList) {
                this.procChildrenHeader(object, entity);
            }
            dataList.add(object);
        }
        return dataList;
    }

    private void procChildrenHeader(JSONObject object, ExcelExportEntity entity) {
        if (HlpUtils.isEmptyList(entity.getList())) {
            return;
        }
        List<JSONObject> objectList = new ArrayList<>();
        JSONObject obj = new JSONObject();
        for (ExcelExportEntity child : entity.getList()) {
            this.procChildrenHeader(obj, child);
            String key = child.getKey().toString();
            obj.put(key, object.get(key));
        }
        objectList.add(obj);
        object.put(entity.getKey().toString(), objectList);
    }

    /**
     * 通用Excel模板导出
     * 参数：template_name 模板名称
     * 参数：url 请求路径
     * 参数：params 请求参数
     * 参数：output_name 文件名称
     * 参数：convert 数据翻译定义（{'需要翻译的字段名称': [{name: '翻译名称', value: '字段值'}]}）
     * 参数：pic_field 图片字段（多个字段以逗号分隔）
     * 参数：pageNo 页码 （查询单页使用）
     * 参数：pageSize 每页记录数（查询单页使用）
     *
     * @return
     */
    @RequestMapping(value = "/exportExcelTemplate.action", method = {RequestMethod.GET, RequestMethod.POST})
    public void exportExcelTemplate(HttpServletResponse response) throws IOException {
        try {
            SysUserExt opUser = super.getSysUser();
            Map<String, Object> map = super.getParams(request);
            // 模版名称
            String templateName = HlpUtils.getString(map, "template_name");
            String url = HlpUtils.getString(map, "url");
            String params = HlpUtils.getString(map, "params");
            String outputName = HlpUtils.getString(map, "output_name");
            String convert = HlpUtils.getString(map, "convert");
            String picField = HlpUtils.getString(map, "pic_field");
            Integer pageNo = HlpUtils.getInteger(map, "pageNo");
            Integer pageSize = HlpUtils.getInteger(map, "pageSize");
            Integer saveFile = HlpUtils.getInteger(map, "save_file", STATE_FILE_NOT_SAVE);
            String data = HlpUtils.getString(map, "data");
            String colForeach = HlpUtils.getString(map, "col_foreach");
            String autoHeight = HlpUtils.getString(map, "auto_height");
            Integer mergedCellWithBorder = HlpUtils.getInteger(map, "merged_cell_with_border", 0);
            super.checkEmpty(templateName, "模板名称：template_name不能为空");
            super.checkEmpty(url, "请求路径：url不能为空");
            super.checkEmpty(params, "请求参数：params不能为空");
            super.checkEmpty(outputName, "文件名称：output_name不能为空");
            if (!HlpUtils.isEmpty(outputName)) {
                outputName = outputName.replaceAll("\\.xlsx", "");
                outputName = outputName.replaceAll("\\.xls", "");
            }
            if (!HlpUtils.isEmpty(pageNo) || !HlpUtils.isEmpty(pageSize)) {
                super.checkEmpty(pageNo, "页码：pageNo不能为空");
                super.checkEmpty(pageSize, "每页记录数：pageSize不能为空");
            }
            Map<String, Object> headers = getExportHeader(map, opUser);
            List<String> picFieldList = new ArrayList<>();
            if (!HlpUtils.isEmpty(picField)) {
                picField = picField.replaceAll("，", ",");
                picFieldList = Arrays.asList(picField.split(","));
            }
            String templateUrl = ResourcesUtil.getExcelTemplatePath(templateName);
            CommonFileExt templateFileExt = commonFileService.queryExcelTemplateByBizSid(templateName);
            if (templateFileExt != null && FileUtil.isFileExist(templateFileExt.getFile_path())) {
                templateUrl = templateFileExt.getFile_path();
            }
            if (!HlpUtils.fileExists(templateUrl)) {
                throw new HlpException("模板不存在");
            }
            // 获取excel模板
            TemplateExportParams exportParams = new TemplateExportParams(templateUrl);
            exportParams.setColForEach(!HlpUtils.isEmpty(colForeach));
            exportParams.setAutoHeight(HlpUtils.isEmpty(autoHeight) || "1".equals(autoHeight));
            Workbook workbook;
            if (map.containsKey(PARAMS_KEY_CUSTOM)) {
                String retJson = WEB_UTILS.postJsonUrl(url, params, headers);
                JSONObject result = JSON.parseObject(retJson);
                if (ErrorCode.CODE_SUCCESS != result.getInteger("code")) {
                    throw new HlpException(result.getString("message"));
                }
                // excel带入参数
                Map<String, Object> excelMap = new HashMap<>(100);
                excelMap.put("data", result.get("data"));
                // 获取excel工作流
                workbook = ExcelExportUtil.exportExcel(exportParams, excelMap);
                workbook.setForceFormulaRecalculation(true);
            } else {
                // 获取导出数据
                List<Object> dataList = this.getExportDataList(url, params, headers, pageNo, pageSize);
                // 数据翻译
                List<JSONObject> convertList = this.convertDataList(dataList, convert);
                //图片替换
                if (!HlpUtils.isEmptyList(picFieldList)) {
                    convertList = this.convertPicData(convertList, picFieldList, true);
                }
                // excel带入参数
                Map<String, Object> excelMap = new HashMap<>(100);
                excelMap.put("list", convertList);
                if (!HlpUtils.isEmpty(data) && JSONUtil.isJsonObj(data)) {
                    excelMap.put("data", JSON.parseObject(data));
                }
                // 获取excel工作流
                workbook = ExcelExportUtil.exportExcel(exportParams, excelMap);
                workbook.setForceFormulaRecalculation(true);
            }
            procFormulaCell(workbook);
            procMergedCellBorder(workbook, 0, mergedCellWithBorder);
            this.procExportExcel(response, workbook, saveFile, outputName);
        } catch (Exception e) {
            LOGGER.error(ExceptionUtil.getStackTraceFromException(e));
            HttpResult<Object> result = new HttpResult<>();
            result.setCode(ErrorCode.CODE_PROC_ERROR);
            result.setMessage(e.getMessage());
            response.setCharacterEncoding("UTF-8");
            response.setHeader("Content-type", "text/json;charset=UTF-8");
            PrintWriter writer = response.getWriter();
            writer.write(JSON.toJSONString(result));
            writer.flush();
        }
    }

    /**
     * 合并单元格边框的处理
     * 由于合并单元格后会出现没有边框的情况，需要将合并的单元格的边框加上
     *
     * @param workbook
     * @param sheetIndex
     */
    private void procMergedCellBorder(Workbook workbook, int sheetIndex, Integer mergedCellWithBorder) {
        try {
            if (mergedCellWithBorder != null && mergedCellWithBorder == 1) {
                Sheet sheet = workbook.getSheetAt(sheetIndex);
                // 获取当前Sheet中所有已经存在的合并单元格信息
                List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
                // 设置合并单元格的边框样式
                for (CellRangeAddress mergedRegion : mergedRegions) {
                    RegionUtil.setBorderTop(BorderStyle.THIN, mergedRegion, sheet);
                    RegionUtil.setBorderBottom(BorderStyle.THIN, mergedRegion, sheet);
                    RegionUtil.setBorderLeft(BorderStyle.THIN, mergedRegion, sheet);
                    RegionUtil.setBorderRight(BorderStyle.THIN, mergedRegion, sheet);
                }
            }
        } catch (Exception e) {
            LOGGER.error(ExceptionUtil.getStackTraceFromException(e));
        }
    }

    /**
     * 生成导出的header数据
     *
     * @param map
     * @param opUser
     * @return
     */
    private Map<String, Object> getExportHeader(Map<String, Object> map, SysUserExt opUser) {
        String token = HlpUtils.getString(map, Constants.REQ_HEADER_NAME_TOKEN);
        String account_set_sid = HttpUtil.getAccountSet(request);
        Map<String, Object> headers = new HashMap<>(2);
        if (HlpUtils.isEmpty(token)) {
            token = opUser.getAccessToken().getToken();
        }
        headers.put(Constants.REQ_HEADER_NAME_TOKEN, token);
        if (!HlpUtils.isEmpty(account_set_sid)) {
            headers.put(Constants.HEADER_NAME_ACCOUNT_SET_SID, account_set_sid);
        }
        return headers;
    }

    private void procExportExcel(HttpServletResponse response, Workbook workbook, Integer saveFile, String outputName) throws Exception {
        if (STATE_FILE_SAVE.equals(saveFile)) {
            List<CommonFile> infoList = new ArrayList<>();
            // 上传的根目录
            String rootPath = Config.getParameterValue(ConfigKeyConstants.UPLOAD_ROOT_PATH);
            File rootAndMidPathFile = new File(rootPath + File.separator + "temp");
            if (!rootAndMidPathFile.exists()) {
                rootAndMidPathFile.mkdirs();
            }
            String datetimeFormatter4FileName = HlpUtils.date2String(new Date(), HlpUtils.Format_Yyyy_MM_dd_HH_mm_ss_FileName);
            String excelFileName = datetimeFormatter4FileName + Math.random() + ".xlsx";
            File excelFile = new File(rootAndMidPathFile.getAbsolutePath() + File.separator + excelFileName);
            FileOutputStream excelStream = new FileOutputStream(excelFile);
            workbook.write(excelStream);
            excelStream.close();
            CommonFile excelFileInfo = new CommonFile();
            excelFileInfo.setOwner_sid("export");
            excelFileInfo.setBiz_type("excel");
            excelFileInfo.setFile_path(excelFile.getAbsolutePath());
            excelFileInfo.setOriginal_file_name(excelFileName);
            excelFileInfo.setState(CommonFileExt.StateNotUsed);
            commonFileService.saveCommonFile(excelFileInfo, null);
            infoList.add(excelFileInfo);
            HttpResult<Object> result = new HttpResult<>();
            result.setData(infoList);
            result.setCode(ErrorCode.CODE_SUCCESS);
            response.setCharacterEncoding("UTF-8");
            response.setHeader("Content-type", "text/json;charset=UTF-8");
            PrintWriter writer = response.getWriter();
            writer.write(JSON.toJSONString(result));
            writer.flush();
        } else {
            assert outputName != null;
            response.setHeader("Content-Disposition", "attachment;filenaxme=" + URLEncoder.encode(outputName, "utf-8") + ".xlsx");
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
            OutputStream output = response.getOutputStream();
            workbook.write(output);
            output.flush();
        }
    }

    /**
     * 读取Excel数据
     *
     * @return
     */
    @ResponseBody
    @RequestMapping(value = "/readExcel.action", method = RequestMethod.POST)
    public HttpResult<Object> readExcel() {
        HttpResult<Object> hr = new HttpResult<>();
        try {
            SysUserExt opUser = super.getSysUser();
            Map<String, Object> map = super.getParams(request);
            String fileId = HlpUtils.getString(map, "file_id");
            String configFile = HlpUtils.getString(map, "config_file");
            Integer lastRowSkipQty = HlpUtils.getInteger(map, "last_row_skip_qty");

            //
            String url = HlpUtils.getString(map, "url");
            Map<String, Object> headers = getExportHeader(map, opUser);
            String params = HlpUtils.getString(map, "params");

            super.checkEmpty(fileId, "Excel文件：file_id不能为空");
            super.checkEmpty(configFile, "Excel配置文件：config_file不能为空");
            CommonFileExt fileExt = commonFileService.queryBySid(fileId);
            super.checkEmpty(fileExt, "Excel文件不存在");
            ExcelConfigFile config = ExcelConfigFile.getInstance();
            if (!HlpUtils.isEmpty(lastRowSkipQty)) {
                config.setLastRowSkipQty(lastRowSkipQty);
            }
            String configError = config.init(configFile);
            if (!HlpUtils.isEmpty(configError)) {
                throw new HlpException(configError);
            }
            ExcelFileUtil<Map<String, Object>> util = ExcelFileUtil.getInstance();
            ExcelObject<Map<String, Object>> eo = util.readExcelObject(fileExt.getFile_path(), config);
            if (eo.getRowError() == null || eo.getRowError().isEmpty()) {
                List<String> list = new ArrayList<>();
                list.add(fileId);
                commonFileService.deleteCommonFiles(list, opUser);
                //
                if (!HlpUtils.isEmpty(url)) {
                    Map<String, Object> objectMap = null;
                    if (!HlpUtils.isEmpty(params)) {
                        objectMap = JSON.parseObject(params);
                    } else {
                        objectMap = new HashMap<>();
                    }
                    objectMap.put("list", eo.getList());
                    params = JSON.toJSONString(objectMap);
                    String retJson = WEB_UTILS.postJsonUrl(url, params, headers);
                    JSONObject result = JSON.parseObject(retJson);
                    if (ErrorCode.CODE_SUCCESS != result.getInteger("code")) {
                        throw new HlpException(result.getString("message"));
                    } else {
                        hr.setMessage((String) result.get("message"));
                        hr.setData(result.get("data"));
                        hr.setCode(ErrorCode.CODE_SUCCESS);
                    }
                } else {
                    hr.setData(eo);
                    hr.setCode(ErrorCode.CODE_SUCCESS);
                }
            } else {
                util.updateExcelFile4Config(eo, fileExt.getFile_path(), config);
                hr.setData(fileId);
                hr.setMessage("读取Excel错误");
                hr.setCode(ErrorCode.CODE_PROC_ERROR);
            }
        } catch (Exception e) {
            super.procException(hr, e);
        }
        return hr;
    }

    private List<Object> getExportDataList(String url, String params, Map<String, Object> headers, Integer pageNo, Integer pageSize) throws Exception {
        PageBean pageBean = new PageBean(1, 100);
        if (!HlpUtils.isEmpty(pageNo) && !HlpUtils.isEmpty(pageSize)) {
            pageBean.setNo(pageNo);
            pageBean.setSize(pageSize);
        }
        Map<String, Object> paramsMap = JSON.parseObject(params);
        paramsMap.put("pageSize", pageBean.getSize());

        // 获取导出数据
        List<Object> dataList = new ArrayList<>();
        do {
            paramsMap.put("pageNo", pageBean.getNo());
            String retJson = WEB_UTILS.postJsonUrl(url, JSON.toJSONString(paramsMap), headers);
            HttpResult<Object> result = JSON.parseObject(retJson, TYPE_REFERENCE);
            if (ErrorCode.CODE_SUCCESS != result.getCode()) {
                throw new HlpException(result.getMessage());
            }
            dataList.addAll(JSON.parseArray(JSON.toJSONString(result.getData())));
            pageBean = result.getPage();
            if (pageBean == null) {
                break;
            }
            pageBean.setNo(pageBean.getNext());
        } while (pageBean.getNo() <= pageBean.getPages());
        if (!HlpUtils.isEmptyList(dataList)) {
            List<Object> newList = new ArrayList<>();
            for (int i = 0; i < dataList.size(); i++) {
                String json = JSON.toJSONString(dataList.get(i));
                JSONObject object = JSON.parseObject(json);
                object.put("_index", i + 1);
                newList.add(object);
            }
            dataList.clear();
            dataList.addAll(newList);
        }
        return dataList;
    }

    private List<JSONObject> convertDataList(List<Object> dataList, String convert) {
        List<JSONObject> resultList = new ArrayList<>();
        if (!HlpUtils.isEmptyList(dataList)) {
            Set<String> convertKeys = new HashSet<>();
            JSONObject convertObj = new JSONObject();
            if (!HlpUtils.isEmpty(convert)) {
                convertObj = JSON.parseObject(convert);
                if (!convertObj.isEmpty()) {
                    convertKeys = convertObj.keySet();
                }
            }
            for (Object object : dataList) {
                String json = JSON.toJSONString(object);
                JSONObject obj = JSON.parseObject(json);
                Set<String> dataKeys = obj.keySet();
                //过滤需要转换的key
                List<String> needConvertKeys = ListUtil.filter(dataKeys, convertKeys::contains);
                if (!HlpUtils.isEmptyList(needConvertKeys)) {
                    for (String key : needConvertKeys) {
                        if (!HlpUtils.isEmpty(obj.get(key))) {
                            List<ExportExcelConvertVo> convertList = ExportExcelConvertVo.parse(convertObj.getJSONArray(key));
                            ExportExcelConvertVo convertVo = ListUtil.get(convertList, t -> t.getValue().equals(obj.get(key)));
                            if (convertVo != null) {
                                obj.put(key, convertVo.getName());
                            }
                        }
                    }
                }
                resultList.add(obj);
            }
        }
        return resultList;
    }

    private List<String> procExcelExportEntity(List<ExcelExportEntity> list) {
        List<String> picFieldList = new ArrayList<>();
        if (!HlpUtils.isEmptyList(list)) {
            for (ExcelExportEntity entity : list) {
                if (HlpUtils.isEmpty(entity.getDatabaseFormat())) {
                    entity.setDatabaseFormat(HlpUtils.Format_YyMMddHHmmss);
                }
                if (entity.getType() == BaseEntityTypeConstants.IMAGE_TYPE) {
                    entity.setExportImageType(1);
                    picFieldList.add(entity.getKey().toString());
                }
                List<String> fieldLsit = this.procExcelExportEntity(entity.getList());
                if (!HlpUtils.isEmptyList(fieldLsit)) {
                    picFieldList.addAll(fieldLsit);
                }
            }
        }
        return picFieldList;
    }

    private List<JSONObject> convertPicData(List<JSONObject> convertList, List<String> picFieldList, boolean isTemplate) {
        if (HlpUtils.isEmptyList(picFieldList)) {
            return convertList;
        }
        List<JSONObject> dataList = new ArrayList<>();
        if (!HlpUtils.isEmptyList(convertList)) {
            List<String> fileIdList = new ArrayList<>();
            for (JSONObject object : convertList) {
                Set<String> dataKeys = object.keySet();
                //过滤需要转换的key
                List<String> needConvertKeys = ListUtil.filter(dataKeys, key -> picFieldList.contains(key) && !HlpUtils.isEmpty(object.get(key)));
                if (HlpUtils.isEmptyList(needConvertKeys)) {
                    dataList.add(object);
                    continue;
                }
                for (String key : dataKeys) {
                    Object value = object.get(key);
                    if (picFieldList.contains(key) && !HlpUtils.isEmpty(value)) {
                        fileIdList.add(String.valueOf(value));
                    }
                }
            }
            List<CommonFileExt> fileInfoList = new ArrayList<>();
            try {
                fileInfoList = commonFileService.getCommonFilesBySidList(fileIdList);
            } catch (Exception e) {
                LOGGER.error(ExceptionUtil.getStackTraceFromException(e));
            }
            if (HlpUtils.isEmptyList(fileInfoList)) {
                return convertList;
            }
            for (JSONObject object : convertList) {
                Set<String> dataKeys = object.keySet();
                //过滤需要转换的key
                List<String> needConvertKeys = ListUtil.filter(dataKeys, key -> picFieldList.contains(key) && !HlpUtils.isEmpty(object.get(key)));
                if (HlpUtils.isEmptyList(needConvertKeys)) {
                    dataList.add(object);
                    continue;
                }
                JSONObject newObj = new JSONObject();
                for (String key : dataKeys) {
                    Object value = object.get(key);
                    CommonFileExt info = ListUtil.get(fileInfoList, t -> t.getId().equals(value));
                    if (picFieldList.contains(key)) {
                        if (info != null && FileUtil.ifExists(info.getFile_path())) {
                            //文件存在才加入，否则报错
                            if (isTemplate) {
                                ImageEntity entity = new ImageEntity();
                                entity.setUrl(info.getFile_path());
                                newObj.put(key, entity);
                            } else {
                                newObj.put(key, info.getFile_path());
                            }
                        }
                    } else {
                        newObj.put(key, value);
                    }
                }
                dataList.add(newObj);
            }
        }
        return dataList;
    }

    private void procFormulaCell(Workbook workbook) {
        if (workbook == null) {
            return;
        }
        workbook.setForceFormulaRecalculation(true);
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
            Sheet sheet = workbook.getSheetAt(sheetNum);
            for (Row row : sheet) {
                for (Cell cell : row) {
                    if (cell == null) {
                        continue;
                    }
                    if (CellType.STRING.equals(cell.getCellType())) {
                        String cellVal = cell.getStringCellValue();
                        if (cellVal.startsWith("formula:")) {
                            cellVal = cellVal.replaceAll("\\$currRow\\$", String.valueOf(row.getRowNum() + 1));
                            cellVal = cellVal.replaceAll("\\$row\\$", String.valueOf(row.getRowNum()));
                            cell.setCellFormula(cellVal.replace("formula:", ""));
                            evaluator.evaluate(cell);
                        }
                    }
                }
            }
        }
    }

    /**
     * 通用Excel模板导出(多页面）
     * 参数：template_name 模板名称
     * 参数：sheets 多页请求参数，是MultiSheetRequestVo数组，包括每页的数据获取和图表输出情况，可以通过设定region将图表输出到同一页的位置
     * 参数：output_name 文件名称
     * 参数：convert 数据翻译定义（{'需要翻译的字段名称': [{name: '翻译名称', value: '字段值'}]}）
     * 参数：pic_field 图片字段（多个字段以逗号分隔）
     * 参数：pageNo 页码 （查询单页使用）
     * 参数：pageSize 每页记录数（查询单页使用）
     * 参数：merged_cell_with_border  合并单元格是否需要边线（0-不需要，1-需要），默认是0
     * 前段调用类似：
     * let sheets =[{url:poiApi.getFullUrl(this.$api.API_FindBuyQcDefectDetailsStatistics),params:queryData},
     * {url:poiApi.getFullUrl(this.$api.API_FindBuyQcDefectDetailsStatisticsGroupBySuppiler),params:queryData,
     * chart_type:'vline',chart_title:'供应商不良率',label_field:'supplier_code',value_field:'defect_rate',chart_region:[5,1,15,21]},
     * {url:poiApi.getFullUrl(this.$api.API_FindBuyQcDefectDetailsStatisticsGroupByDefectType),params:queryData,
     * chart_type:'pie',chart_title:'不良类型分布图',label_field:'defect_type_value',value_field:'defect_qty',chart_region:[4,1,12,13]}
     * ];
     * poiApi.exportExcelTemplateMultiSheet(stasticsApi.excelExportQcDefectChartStatisticsTeamplateName, sheets, '不良统计', null);
     *
     * @return
     */
    @RequestMapping(value = "/exportExcelTemplateMultiSheet.action", method = {RequestMethod.GET, RequestMethod.POST})
    public void exportExcelTemplateMultiSheet(HttpServletResponse response) throws IOException {
        try {
            SysUserExt opUser = super.getSysUser();
            Map<String, Object> map = super.getParams(request);
            String token = HlpUtils.getString(map, Constants.REQ_HEADER_NAME_TOKEN);
            // 模版名称
            String templateName = HlpUtils.getString(map, "template_name");
            String sheets = HlpUtils.getString(map, "sheets");
            super.checkEmpty(sheets, "缺少参数sheets");
            List<MultiSheetRequestVo> sheetList = JSON.parseArray(sheets, MultiSheetRequestVo.class);
            if (sheetList == null || sheetList.isEmpty()) {
                throw new Exception("sheets不能为空");
            }
            for (int i = 0; i < sheetList.size(); i++) {
                MultiSheetRequestVo sheetRequestVo = sheetList.get(i);
                super.checkEmpty(sheetRequestVo.getUrl(), "sheets[" + i + "]的url不能为空");
                super.checkEmpty(sheetRequestVo.getParams(), "sheets[" + i + "]的params不能为空");
            }
            Map<String, Object> headers = getExportHeader(map, opUser);
            String outputName = HlpUtils.getString(map, "output_name");
            String convert = HlpUtils.getString(map, "convert");
            String className = HlpUtils.getString(map, "class_name");
            String picField = HlpUtils.getString(map, "pic_field");
            Integer pageNo = HlpUtils.getInteger(map, "pageNo");
            Integer pageSize = HlpUtils.getInteger(map, "pageSize");
            Integer saveFile = HlpUtils.getInteger(map, "save_file", STATE_FILE_NOT_SAVE);
            String data = HlpUtils.getString(map, "data");
            String colForeach = HlpUtils.getString(map, "col_foreach");
            Integer mergedCellWithBorder = HlpUtils.getInteger(map, "merged_cell_with_border", 0);
            super.checkEmpty(templateName, "模板名称：template_name不能为空");
            super.checkEmpty(outputName, "文件名称：output_name不能为空");
            if (!HlpUtils.isEmpty(outputName)) {
                outputName = outputName.replaceAll("\\.xlsx", "");
                outputName = outputName.replaceAll("\\.xls", "");
            }
            if (!HlpUtils.isEmpty(pageNo) || !HlpUtils.isEmpty(pageSize)) {
                super.checkEmpty(pageNo, "页码：pageNo不能为空");
                super.checkEmpty(pageSize, "每页记录数：pageSize不能为空");
            }
            List<String> picFieldList = new ArrayList<>();
            if (!HlpUtils.isEmpty(picField)) {
                picField = picField.replaceAll("，", ",");
                picFieldList = Arrays.asList(picField.split(","));
            }
            String templateUrl = ResourcesUtil.getExcelTemplatePath(templateName);
            if (!HlpUtils.fileExists(templateUrl)) {
                throw new HlpException("模板不存在");
            }
            Integer[] sheetNums = new Integer[sheetList.size()];
            for (int i = 0; i < sheetList.size(); i++) {
                sheetNums[i] = i;
            }
            // 获取excel模板
            TemplateExportParams exportParams = new TemplateExportParams(templateUrl);
            exportParams.setSheetNum(sheetNums);
            exportParams.setColForEach(!HlpUtils.isEmpty(colForeach));
            Workbook workbook;
            Map<Integer, Map<String, Object>> sheetDataList = new HashMap<>();
            if (map.containsKey(PARAMS_KEY_CUSTOM)) {
                if (HlpUtils.isEmpty(token)) {
                    token = opUser.getAccessToken().getToken();
                }
                headers.put(Constants.REQ_HEADER_NAME_TOKEN, token);
                for (int i = 0; i < sheetList.size(); i++) {
                    MultiSheetRequestVo sheetRequestVo = sheetList.get(i);
                    Map<String, Object> paramsMap = sheetRequestVo.getParams();
                    paramsMap.put("export_custom", "1");
                    String retJson = WEB_UTILS.postJsonUrl(sheetRequestVo.getUrl(), JSON.toJSONString(paramsMap), headers);
                    JSONObject result = JSON.parseObject(retJson);
                    if (ErrorCode.CODE_SUCCESS != result.getInteger("code")) {
                        throw new HlpException(result.getString("message"));
                    }
                    // excel带入参数
                    Map<String, Object> excelMap = new HashMap<>(100);
                    if (HlpUtils.isEmpty(className)) {
                        excelMap.put("data", result.get("data"));
                    } else {
                        excelMap.put("data", JSON.parseObject(JSON.toJSONString(result.get("data")), Class.forName(className)));
                    }
                    sheetDataList.put(i, excelMap);
                }
                // 获取excel工作流
                workbook = ExcelExportUtil.exportExcel(sheetDataList, exportParams);
            } else {
                // 获取导出数据
                for (int i = 0; i < sheetList.size(); i++) {
                    MultiSheetRequestVo sheetRequestVo = sheetList.get(i);
                    Map<String, Object> paramsMap = sheetRequestVo.getParams();
                    List<Object> dataList = this.getExportDataList(sheetRequestVo.getUrl(), JSON.toJSONString(paramsMap), headers, pageNo, pageSize);
                    // 数据翻译
                    List<JSONObject> convertList = this.convertDataList(dataList, convert);
                    //图片替换
                    if (!HlpUtils.isEmptyList(picFieldList)) {
                        convertList = this.convertPicData(convertList, picFieldList, true);
                    }
                    // excel带入参数
                    Map<String, Object> excelMap = new HashMap<>(100);
                    excelMap.put("list", convertList);
                    if (!HlpUtils.isEmpty(data) && JSONUtil.isJsonObj(data)) {
                        excelMap.put("data", JSON.parseObject(data));
                    }
                    sheetDataList.put(i, excelMap);
                }
                // 获取excel工作流
                workbook = ExcelExportUtil.exportExcel(sheetDataList, exportParams);
            }
            this.procNumericCell(workbook);
            this.procBlankCell(workbook);
            this.procFormulaCell(workbook, "formula:");
            this.procFormulaCell(workbook, "next_formula:");
            if (workbook != null) {
                procChartExport(workbook, sheetList, sheetDataList);
                for (int i = 0; i < sheetList.size(); i++) {
                    procMergedCellBorder(workbook, i, mergedCellWithBorder);
                }
                this.procExportExcel(response, workbook, saveFile, outputName);
            }
        } catch (Exception e) {
            LOGGER.error(ExceptionUtil.getStackTraceFromException(e));
            HttpResult<Object> result = new HttpResult<>();
            result.setCode(ErrorCode.CODE_PROC_ERROR);
            result.setMessage(e.getMessage());
            response.setCharacterEncoding("UTF-8");
            response.setHeader("Content-type", "text/json;charset=UTF-8");
            PrintWriter writer = response.getWriter();
            writer.write(JSON.toJSONString(result));
            writer.flush();
        }
    }

    /**
     * 处理图表输出
     *
     * @param workbook
     * @param sheetList
     * @param sheetDataList
     * @throws HlpException
     */
    @SuppressWarnings("unchecked")
    private void procChartExport(Workbook workbook, List<MultiSheetRequestVo> sheetList, Map<Integer, Map<String, Object>> sheetDataList) throws HlpException {

        for (int i = 0; i < sheetList.size(); i++) {
            //逐个检查页面
            MultiSheetRequestVo vo = sheetList.get(i);
            if (!HlpUtils.isEmpty(vo.getChart_type())) {
                Sheet sheet = workbook.getSheetAt(i);
                if (sheet instanceof XSSFSheet) {
                    List<String> ss = new ArrayList<>();
                    List<Number> val = new ArrayList<>();
                    Map<String, Object> sheetData = sheetDataList.get(i);
                    Object row = sheetData.get("list");
                    if (row != null) {
                        List<JSONObject> convertList = (List<JSONObject>) row;
                        if (convertList.isEmpty()) {
                            continue;
                        }
                        for (JSONObject json : convertList) {
                            ss.add(json.getString(vo.getLabel_field()));
                            val.add(json.getDouble(vo.getValue_field()));
                        }
                        int[] region = vo.getChart_region();
                        if (region == null || region.length < 4) {
                            region = new int[]{5, 5, 10, 10};
                        }
                        ChartTypes chartType;
                        if (MultiSheetRequestVo.CHART_TYPE_PIE.equals(vo.getChart_type())) {
                            chartType = ChartTypes.PIE;
                        } else {
                            chartType = ChartTypes.BAR;
                        }
                        exportDiagram(chartType, (XSSFSheet) sheet, vo.getChart_title(), ss, val, region[0], region[1], region[2], region[3]);
                    }
                } else {
                    throw new HlpException("输出图表，必须是.XLSX格式");
                }
            }
        }
    }

    /**
     * 导出图表
     *
     * @param chartType
     * @param sheet
     * @param titleText
     * @param keylist
     * @param vList
     */
    public void exportDiagram(ChartTypes chartType, XSSFSheet sheet, String titleText, List<String> keylist, List<Number> vList, int col_begin, int row_begin, int col_end, int row_end) {
        // 创建一个画布
        XSSFDrawing drawing = sheet.createDrawingPatriarch();
        // 前四个默认0，col_begin, row_begin, col_end, row_end是开始列号、行号、结束列好、行号
        // 默认宽度(14-8)*12
        XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, col_begin, row_begin, col_end, row_end);
        // 创建一个chart对象
        XSSFChart chart = drawing.createChart(anchor);
        // 标题
        chart.setTitleText(titleText);
        // 标题是否覆盖图表
        chart.setTitleOverlay(false);

        //XDDFDataSource<String> countries = XDDFDataSourcesFactory.fromStringCellRange(sheet, new CellRangeAddress(0, 0, 0, 6));//从指定表格中的位置上获取对应数据
        XDDFCategoryDataSource categoryDataSource = XDDFDataSourcesFactory.fromArray(keylist.toArray(new String[0]));
        // 数据1，单元格范围位置[1, 0]到[1, 6]
        // XDDFNumericalDataSource<Double> area = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(1, 1, 0, 6));
        XDDFNumericalDataSource<Number> numericalDataSource = XDDFDataSourcesFactory.fromArray(vList.toArray(new Number[0]));
        if (ChartTypes.BAR.equals(chartType) || ChartTypes.BAR3D.equals(chartType)) {
            exportDiagramBar(chart, categoryDataSource, numericalDataSource, titleText);
        } else if (ChartTypes.PIE.equals(chartType) || ChartTypes.PIE3D.equals(chartType)) {
            exportDiagramPie(chart, categoryDataSource, numericalDataSource);
        }
        // 打印图表的xml
//        System.out.println(chart.getCTChart());

    }

    /**
     * 导出饼图
     *
     * @param chart
     * @param categoryDataSource
     * @param numericalDataSource
     */
    private void exportDiagramPie(XSSFChart chart, XDDFCategoryDataSource categoryDataSource, XDDFNumericalDataSource<Number> numericalDataSource) {
        // 图例位置
        XDDFChartLegend legend = chart.getOrAddLegend();
        legend.setPosition(LegendPosition.TOP_RIGHT);
        XDDFChartData data = chart.createData(ChartTypes.PIE, null, null);
//        XDDFChartData data = chart.createData(ChartTypes.PIE, null, null);
        // 设置为可变颜色
        data.setVaryColors(true);
        // 图表加载数据
        data.addSeries(categoryDataSource, numericalDataSource);
        // 绘制
        chart.plot(data);
        CTDLbls dLbls = chart.getCTChart().getPlotArea().getPieChartArray(0).getSerArray(0).addNewDLbls();
        dLbls.addNewShowVal().setVal(false);
        dLbls.addNewShowLegendKey().setVal(false);
        dLbls.addNewShowCatName().setVal(true);// 类别名称
        dLbls.addNewShowSerName().setVal(false);
        dLbls.addNewShowPercent().setVal(true);// 百分比
        dLbls.addNewShowLeaderLines().setVal(true);// 引导线
        dLbls.setSeparator("\n");// 分隔符为分行符
        dLbls.addNewDLblPos().setVal(org.openxmlformats.schemas.drawingml.x2006.chart.STDLblPos.Enum.forString("inEnd"));
        // 数据标签内
    }

    /**
     * 导出柱状图
     *
     * @param chart
     * @param categoryDataSource
     * @param numericalDataSource
     * @param titleText
     */
    private void exportDiagramBar(XSSFChart chart, XDDFCategoryDataSource categoryDataSource, XDDFNumericalDataSource<Number> numericalDataSource, String titleText) {
        // 图例位置
        XDDFChartLegend legend = chart.getOrAddLegend();
        legend.setPosition(LegendPosition.TOP);

        // 分类轴标(X轴),标题位置
        XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
        //    bottomAxis.setTitle("国家");
        // 值(Y轴)轴,标题位置
        XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
        // bar：条形图，
        XDDFBarChartData bar = (XDDFBarChartData) chart.createData(ChartTypes.BAR, bottomAxis, leftAxis);

        leftAxis.setCrossBetween(AxisCrossBetween.BETWEEN);
        // 设置为可变颜色
        bar.setVaryColors(true);// 如果需要设置成自己想要的颜色，这里可变颜色要设置成false
        // 条形图方向，纵向/横向：纵向
        bar.setBarDirection(BarDirection.COL);
        // 图表加载数据，条形图1
        XDDFBarChartData.Series series1 = (XDDFBarChartData.Series) bar.addSeries(categoryDataSource, numericalDataSource);
        // 条形图例标题
        series1.setTitle(titleText, null);
//            XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(PresetColor.BLUE));
//            // 条形图，填充颜色
//            series1.setFillProperties(fill);
        // 绘制
        chart.plot(bar);
    }

    private void procNumericCell(Workbook workbook) {
        if (workbook == null) {
            return;
        }
        for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
            Sheet sheet = workbook.getSheetAt(sheetNum);
            for (Row row : sheet) {
                for (Cell cell : row) {
                    if (cell == null) {
                        continue;
                    }
                    if (CellType.STRING.equals(cell.getCellType())) {
                        String cellVal = cell.getStringCellValue();
                        if (cellVal.startsWith("num:")) {
                            cellVal = cellVal.replace("num:", "");
                            cell.setCellValue(Double.parseDouble(cellVal));
                        }
                    }
                }
            }
        }
    }

    private void procBlankCell(Workbook workbook) {
        if (workbook == null) {
            return;
        }
        for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
            Sheet sheet = workbook.getSheetAt(sheetNum);
            for (Row row : sheet) {
                for (Cell cell : row) {
                    if (cell == null) {
                        continue;
                    }
                    Object cellVal = null;
                    if (CellType.STRING.equals(cell.getCellType())) {
                        cellVal = cell.getStringCellValue();
                    } else if (CellType.NUMERIC.equals(cell.getCellType())) {
                        cellVal = cell.getNumericCellValue();
                    } else if (CellType.FORMULA.equals(cell.getCellType())) {
                        cellVal = cell.getCellFormula();
                    } else if (CellType.BOOLEAN.equals(cell.getCellType())) {
                        cellVal = cell.getBooleanCellValue();
                    }
                    if (HlpUtils.isEmpty(cellVal)) {
                        cell.setBlank();
                    }
                }
            }
        }
    }

    private void procFormulaCell(Workbook workbook, String format) {
        if (workbook == null) {
            return;
        }
        workbook.setForceFormulaRecalculation(true);
        for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
            Sheet sheet = workbook.getSheetAt(sheetNum);
            for (Row row : sheet) {
                for (Cell cell : row) {
                    if (cell == null) {
                        continue;
                    }
                    if (CellType.STRING.equals(cell.getCellType())) {
                        String cellVal = cell.getStringCellValue();
                        if (cellVal.startsWith(format)) {
                            cellVal = cellVal.replaceAll("\\$currRow\\$", String.valueOf(row.getRowNum() + 1));
                            cellVal = cellVal.replaceAll("\\$row\\$", String.valueOf(row.getRowNum()));
                            cell.setCellFormula(cellVal.replace(format, ""));
                        }
                    }
                }
            }
        }
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        evaluator.evaluateAll();
    }


    /**
     * 多表头导出网络内容到Excel中
     * 表头格式：[{"name":"", "value":"", children:[]}]
     *
     * @return
     */
    @RequestMapping(value = "/exportExcel4MultiHead.action", method = RequestMethod.POST)
    public void exportExcel4MultiHead(HttpServletResponse response) throws IOException {
        try {
            SysUserExt opUser = super.getSysUser();
            Map<String, Object> map = super.getParams(request);
            request.setAttribute("params4Log", map);
            String url = HlpUtils.getString(map, "url");
            Object param = map.get("param");
            Integer all = HlpUtils.getInteger(map, "all", 1);
            Object header = map.get("header");
            String templateName = HlpUtils.getString(map, "templateName");
            String outputName = HlpUtils.getString(map, "outputName");
            Object titleField = map.get("titleField");
            String firstDataRow = HlpUtils.getString(map, "firstDataRow");
            Integer saveFile = HlpUtils.getInteger(map, "save_file", STATE_FILE_NOT_SAVE);
            int intFirstDataRow = 1;
            List<Object> titleList = new ArrayList<>();
            if (!HlpUtils.isEmpty(titleField)) {
                if (titleField instanceof List) {
                    titleList = JSON.parseArray(JSON.toJSONString(titleField));
                } else if (titleField instanceof String) {
                    if (((String) titleField).startsWith("[")) {
                        titleList = JSON.parseArray((String) titleField);
                    } else if (((String) titleField).startsWith("{")) {
                        LinkedHashMap<String, String> alias = JSON.parseObject((String) titleField, Constants.LINKED_HASH_MAP_STRING_STRING_TYPE_REFERENCE);
                        if (alias.containsKey("name") && alias.containsKey("value")) {
                            titleList.add(alias);
                        } else {
                            for (Map.Entry<String, String> item : alias.entrySet()) {
                                LinkedHashMap<String, String> title = new LinkedHashMap<>();
                                title.put("name", item.getValue());
                                title.put("value", item.getKey());
                                titleList.add(title);
                            }
                        }
                    }
                } else if (titleField instanceof JSONObject) {
                    JSONObject object = (JSONObject) titleField;
                    for (Map.Entry<String, Object> item : object.entrySet()) {
                        LinkedHashMap<String, String> title = new LinkedHashMap<>();
                        title.put("name", (String) item.getValue());
                        title.put("value", item.getKey());
                        titleList.add(title);
                    }
                }
            }
            if (!HlpUtils.isEmpty(firstDataRow)) {
                intFirstDataRow = (int) Double.parseDouble(firstDataRow);
            }
            super.checkEmpty(url, "请提供参数url");
            super.checkEmpty(param, "请提供参数param");
            Map<String, Object> params = JSON.parseObject(JSON.toJSONString(param));
            HlpUtils.copyStringValue(map, params, "dataSource", "dataSource", false, false);
            if (!params.containsKey("nopage")) {
                PageBean page = new PageBean(params);
                params.put("pageNo", page.getNo());
                params.put("pageSize", page.getSize());
            }
            Map<String, Object> headerMap;
            if (!HlpUtils.isEmpty(header)) {
                if (header instanceof String) {
                    headerMap = JSON.parseObject((String) header);
                } else {
                    headerMap = JSON.parseObject(JSON.toJSONString(header));
                }
            } else {
                headerMap = this.getExportHeader(map, opUser);
            }
            String getDataJson = WEB_UTILS.postJsonUrl(url, JSON.toJSONString(params), headerMap);
            HttpResult<Object> result = JSON.parseObject(getDataJson, TYPE_REFERENCE);
            if (ErrorCode.CODE_SUCCESS != result.getCode()) {
                RequestUtils.writeResponse(response, result);
                return;
            }
            if (!HlpUtils.isEmpty(templateName)) {
                templateName = ResourcesUtil.getXlsxPath(templateName);
            }
            ExcelExportHelper helper = Export.createHelper(templateName, intFirstDataRow);
            if (HlpUtils.isEmpty(templateName)) {
                Export.setExcelHeader4MultiHead(helper, titleList, map.containsKey("freezeTitle"), intFirstDataRow);
            }
            List<LinkedHashMap<String, Object>> dataList = new ArrayList<>();
            while (ErrorCode.CODE_SUCCESS == result.getCode()) {
                if (result.getData() == null) {
                    break;
                }
                String json = JSON.toJSONString(result.getData());
                JSONArray array = JSON.parseArray(json);
                if (array.isEmpty()) {
                    break;
                }
                List<LinkedHashMap<String, Object>> list = new ArrayList<>();
                for (int i = 0; i < array.size(); i++) {
                    Map<String, Object> objectMap = array.getJSONObject(i);
                    LinkedHashMap<String, Object> linkedHashMap = new LinkedHashMap<>(objectMap);
                    list.add(linkedHashMap);
                    dataList.add(linkedHashMap);
                }
                if (HlpUtils.isEmptyList(titleList)) {
                    LinkedHashMap<String, Object> tmap = new LinkedHashMap<>();
                    for (String string : list.get(0).keySet()) {
                        tmap.put(string, string);
                    }
                    titleList.add(tmap);
                }
                if (!HlpUtils.isEmpty(titleList)) {
                    Export.setRows(helper, list, titleList, intFirstDataRow);
                }
                PageBean pageBean = result.getPage();
                if (pageBean != null && all == 1) {
                    if (pageBean.getNo() * pageBean.getSize() < pageBean.getTotalRows()) {
                        params.put("pageNo", pageBean.getNo() + 1);
                        params.put("pageSize", pageBean.getSize());
                        getDataJson = WEB_UTILS.postJsonUrl(url, JSON.toJSONString(params), headerMap);
                        result = JSON.parseObject(getDataJson, TYPE_REFERENCE);
                    } else {
                        break;
                    }
                } else {
                    break;
                }
            }
            if (map.containsKey("skipEmpty") || (helper.getRowIndex() - intFirstDataRow > 0)) {
                Export.setTotalRows(helper, titleList, intFirstDataRow);

                // 导出Excel表格后追加内容
                if (map.containsKey("extMergedRow")) {
                    JSONArray array = JSON.parseArray(JSON.toJSONString(map.get("extMergedRow")));
                    Export.setExtMergedRow(helper, titleList, array, intFirstDataRow);
                }

                // 导出Excel表格任意位置改写内容
                if (map.containsKey("updateRow")) {
                    JSONArray array = JSON.parseArray(JSON.toJSONString(map.get("updateRow")));
                    Export.updateRow(helper, array);
                }

                // 设置表格合并
                if (map.containsKey("mergedRegion")) {
                    JSONObject object = JSON.parseObject(JSON.toJSONString(map.get("mergedRegion")));
                    Export.setRowMergedRegion(helper, titleList, object);
                }

                // 重置边框样式
                if (map.containsKey("resetBorder")) {
                    JSONArray array = JSON.parseArray(JSON.toJSONString(map.get("resetBorder")));
                    Export.resetTableBorder(helper, array);
                }

                // 设置表格外边框样式
                if (map.containsKey("tableBorder")) {
                    JSONArray array = JSON.parseArray(JSON.toJSONString(map.get("tableBorder")));
                    if (!array.isEmpty()) {
                        for (int i = 0; i < array.size(); i++) {
                            Export.setTableBorder(helper, array.getJSONObject(i));
                        }
                    }
                }

                if (map.containsKey("zoom")) {
                    int zoom = HlpUtils.getInteger(map, "zoom", 100);
                    helper.getSheet().setZoom(zoom);
                }

                // 表格行合并
                if (map.containsKey("rowRegion")) {
                    JSONArray array = JSON.parseArray(JSON.toJSONString(map.get("rowRegion")));
                    if (!array.isEmpty()) {
                        for (int i = 0; i < array.size(); i++) {
                            Export.setMergedRowRegion(helper, titleList, dataList, array.getJSONObject(i), intFirstDataRow);
                        }
                    }
                }

                if (map.containsKey("hiddenColumn")) {
                    JSONArray array = JSON.parseArray(JSON.toJSONString(map.get("hiddenColumn")));
                    if (!array.isEmpty()) {
                        Row firstRow = helper.getSheet().getRow(0);
                        if (firstRow != null) {
                            int maxColumn = firstRow.getLastCellNum();
                            for (int i = 0; i < array.size(); i++) {
                                int colIndex = array.getInteger(i);
                                if (colIndex > maxColumn) {
                                    continue;
                                }
                                helper.getSheet().setColumnHidden(colIndex, true);
                            }
                        }
                    }
                }
                if (map.containsKey("lockColumn")) {
                    Export.setLockColumn(helper, map.get("lockColumn"), intFirstDataRow);
                }
                if (!HlpUtils.isEmpty(outputName)) {
                    Export.exportExcel(helper, response, outputName);
                    return;
                }
            }
            Workbook workbook = helper.getWorkbook();
            procFormulaCell(workbook);
            this.procExportExcel(response, workbook, saveFile, outputName);
        } catch (Exception e) {
            LOGGER.error(ExceptionUtil.getStackTraceFromException(e));
            HttpResult<Object> result = new HttpResult<>();
            result.setCode(ErrorCode.CODE_PROC_ERROR);
            result.setMessage(e.getMessage());
            response.setCharacterEncoding("UTF-8");
            response.setHeader("Content-type", "text/json;charset=UTF-8");
            PrintWriter writer = response.getWriter();
            writer.write(JSON.toJSONString(result));
            writer.flush();
        }
    }
}
